package com.dao.impl;

import com.dao.SportingEventsDAO;
import com.entity.AdminInfo;
import com.entity.SportingEvents;
import util.DBUtil;

import java.io.Serializable;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class SportingEventsDAOImpl implements SportingEventsDAO {
    @Override
    public int insert(SportingEvents entity) throws SQLException {
        String sql = "INSERT INTO sporting_events(se_id,se_name,se_desc) values(?,?,?)";
        try(
                Connection conn = DBUtil.getConnection();
                PreparedStatement pstmt = conn.prepareStatement(sql);
        ){
            pstmt.setObject(1, entity.getSeId());
            pstmt.setObject(2,entity.getSeName());
            pstmt.setObject(3,entity.getSeDesc());
            return pstmt.executeUpdate();
        }
    }

    @Override
    public int updateById(SportingEvents entity) throws SQLException {
        String sql = "update sporting_events set se_name=?,se_desc=? where se_id=?";
        try(
                Connection conn = DBUtil.getConnection();
                PreparedStatement pstmt = conn.prepareStatement(sql);
        ){
            pstmt.setObject(3, entity.getSeId());
            pstmt.setObject(1,entity.getSeName());
            pstmt.setObject(2,entity.getSeDesc());
            return pstmt.executeUpdate();
        }
    }

    @Override
    public int deleteById(Serializable id) throws SQLException {
        String sql = "delete from sporting_events  where se_id=?";
        try(
                Connection conn = DBUtil.getConnection();
                PreparedStatement pstmt = conn.prepareStatement(sql);
        ){
            pstmt.setObject(1, id);
            return pstmt.executeUpdate();
        }
    }

    @Override
    public SportingEvents selectById(Serializable id) throws SQLException {
        String sql = "select * from sporting_events  where se_id=?";
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        try{
            conn = DBUtil.getConnection();
            pstmt = conn.prepareStatement(sql);
            pstmt.setObject(1, id);
            rs = pstmt.executeQuery();
            if(rs.next()){
                SportingEvents entity = new SportingEvents();
                entity.setSeId(rs.getInt("se_id"));
                //如果该字段可能为空
                entity.setSeName(rs.getString("se_name"));
                entity.setSeDesc(rs.getString("se_desc"));
                return entity;
            }else{
                throw new SQLException("没有找到对应的记录");
            }
        }finally {
            DBUtil.close(conn,pstmt,rs);
        }
    }

    @Override
    public List<SportingEvents> select(int index, int len) throws SQLException {
        String sql = "select * from sporting_events  order by se_id limit ?,?";//只要是返回多条记录,一定记得+order by子句
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        try{
            conn = DBUtil.getConnection();
            pstmt = conn.prepareStatement(sql);
            pstmt.setObject(1, index);
            pstmt.setObject(2, len);
            rs = pstmt.executeQuery();
            List<SportingEvents> list = new ArrayList<>();
            while (rs.next()) {
                SportingEvents entity = new SportingEvents();
                entity.setSeId(rs.getInt("se_id"));
                //如果该字段可能为空
                entity.setSeName(rs.getString("se_name"));
                entity.setSeDesc(rs.getString("se_desc"));
                list.add(entity);
            }
            return list;
        }finally {
            DBUtil.close(conn,pstmt,rs);
        }
    }

    @Override
    public long count() throws SQLException {
        String sql = "select count(*) from sporting_events";
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        try{
            conn = DBUtil.getConnection();
            pstmt = conn.prepareStatement(sql);
            rs = pstmt.executeQuery();
            if(rs.next()){
                return rs.getLong(1);
            }else{
                throw new SQLException("没有找到对应的记录");
            }
        }finally {
            DBUtil.close(conn,pstmt,rs);
        }
    }
}
